/**
 * Created by jackliu on 15/12/16.
 */

var common = require('../../common');
var pool = require('../index');
var mysql = require('mysql');


var ItbBaseModel = function(obj){
    if(obj)
        common.copyProperty(this, obj);
};
// 取得表名（需要各派生类进行重载）
ItbBaseModel.prototype.getTableName = function(){
    return '';
}
// 取得查询语句中where 后面匹配主键的条件（需要各派生类进行重载）
ItbBaseModel.prototype.getWhereConditionOfKeyEqual = function(){
    return '1=0';
}
// 取得查询语句中where 后面的条件
ItbBaseModel.prototype.getWhereCondition = function(){
    var ret = "1=1";
    for (var key in this){
        var value = this[key];
        if(value!=null && (typeof value!=='function'))
            ret +=(" and `"+key+"`="+"'"+value+"'");
    }

    return ret;

}
// 取得排序方法（需要各派生类进行重载）
ItbBaseModel.prototype.getOrderBy = function(){
    return '';
}
// 新建到DB中
// callback 参数：
//                error：错误
//                id：db的id
ItbBaseModel.prototype.saveToDB = function(callback){
    var THIS = this;
    var tableName = this.getTableName();
    pool.pool.getConnection(function(err, connection) {
        // Use the connection
        var sql = 'INSERT INTO '+tableName+' SET ? ,create_time=sysdate(),update_time=sysdate()';
        var sqlValue = [ THIS ];
        var sql = mysql.format(sql, sqlValue);
        console.log(sql);
        connection.query(sql, function(err, result) {
            // And done with the connection.
            connection.release();

            if (err)
                console.log(err);

            callback(err, result.insertId);
        });
    });

}
// 更新到DB中
// callback 参数：
//                error：错误
ItbBaseModel.prototype.updateToDB = function(callback){

    var THIS = this;
    var tableName = this.getTableName();
    //mysql update
    var whereConditionOfKeyEqual = THIS.getWhereConditionOfKeyEqual();
    pool.pool.getConnection(function(err, connection) {
        // Use the connection

        var sql = 'UPDATE '+tableName+' SET ? ,update_time=sysdate() WHERE '+whereConditionOfKeyEqual;
        var sqlValue = [ THIS ];
        var sql = mysql.format(sql, sqlValue);
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();

            if(error) {
                console.log('ItbBaseModel.prototype.updateToDB error:'+error);
                callback(error);
            } else {
                callback(null);
            }
        });
    });
};

// arrUpdSysDtFieldNm: 需更新成sysdate()的字段名数组: ["finish_time","update_time"]
ItbBaseModel.prototype.updateToDB_SysDt = function(callback){

    var THIS = this;
    var tableName = this.getTableName();
    //mysql update
    var whereConditionOfKeyEqual = THIS.getWhereConditionOfKeyEqual();
    pool.pool.getConnection(function(err, connection) {
        // Use the connection

        var sql_0 = '';
        if(THIS.arrUpdSysDtFieldNm) {
            for(var i=0;i<THIS.arrUpdSysDtFieldNm.length;i++){
                var nm = THIS.arrUpdSysDtFieldNm[i];
                if(nm && nm.length>0) {
                    sql_0 += ','+nm+'=sysdate() ';
                }
            }
            delete THIS.arrUpdSysDtFieldNm;
        }

        var sql = 'UPDATE '+tableName+' SET ? ,update_time=sysdate() '+sql_0+' WHERE '+whereConditionOfKeyEqual;
        var sqlValue = [ THIS ];
        var sql = mysql.format(sql, sqlValue);
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();

            if(error) {
                console.log('ItbBaseModel.prototype.updateToDB_SysDt error:'+error);
                callback(error);
            } else {
                callback(null);
            }
        });
    });
};

// 查找
// callback 参数：
//                error：错误
//                count：找到的个数
ItbBaseModel.prototype.findCountFromDB = function(callback){
    var THIS = this;
    var tableName = this.getTableName();
    var whereCondition = THIS.getWhereCondition();
    // 从mysql中查询
    pool.pool.getConnection(function(err, connection) {
        var sql = "select count(*) as itemCount from "+tableName+" t1 where "+whereCondition;
        var sqlValue = [];
        var sql = mysql.format(sql, sqlValue);
        console.log(sql);

        // Use the connection
        connection.query(sql, function(error, items) {
            // And done with the connection.
            connection.release();

            var total=0;
            if(error) {
                console.log(error);
            } else if (items) {
                console.log('find item');
                total = items[0].itemCount;
            } else{
                console.log('find no one');
            }
            // 回调
            callback(error, total);
        });

    });

}

// 查找
// callback 参数：
//                error：错误
//                items：找到所有对象
ItbBaseModel.prototype.findFromDB = function(callback,skipCnt,pageSize){
    var THIS = this;
    var tableName = this.getTableName();
    var whereCondition = THIS.getWhereCondition();
    var orderBy = THIS.getOrderBy();
    // 从mysql中查询
    pool.pool.getConnection(function(err, connection) {
        var sql = "select *  from "+tableName+" t1 where "+whereCondition;
        if(orderBy && orderBy.length>0){
            sql += (" order by "+orderBy);
        }
        if(typeof skipCnt!='undefined' && skipCnt != null && typeof pageSize!='undefined' && pageSize != null){
            sql += " limit " + Number(skipCnt) + "," + Number(pageSize);
        }

        var sqlValue = [];
        var sql = mysql.format(sql, sqlValue);
        console.log(sql);

        // Use the connection
        connection.query(sql, function(error, items) {
            // And done with the connection.
            connection.release();

            // 回调
            callback(error, items);
        });

    });
};

ItbBaseModel.prototype.getTimeFromDB = function(callback){
    var THIS = this;
    // 从mysql中查询
    pool.pool.getConnection(function(err, connection) {
        var sql = " SELECT DATE_FORMAT(sysdate(), '%Y-%m-%d %H:%i:%S') AS str_sysdate "+
            " ,DATE_FORMAT(now(), '%Y-%m-%d %H:%i:%S') AS str_now "+
            " ,UNIX_TIMESTAMP(now()) AS ts_now "+
            " FROM DUAL ";
        console.log(sql);

        // Use the connection
        connection.query(sql, function(error, items) {
            // And done with the connection.
            connection.release();

            // 回调
            if(items && items.length>0) {
                callback(error, items[0]);
            } else {
                callback(error, null);
            }
        });
    });
};

// 导出
module.exports = ItbBaseModel;